package top.zhangjianyong.test.waterrate;

import cn.hutool.core.util.StrUtil;
import org.apache.commons.io.IOUtils;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.*;
import java.util.HashSet;
import java.util.List;
import java.util.Set;

public class WaterRate {

    public static void main(String[] args) throws IOException, InvalidFormatException {
        File big15 = new File("/Users/zhangjianyong/IdeaProjects/waterrate/water.txt");
        List<String> list = IOUtils.readLines(new FileReader(big15));

        Set<String> set = new HashSet<>(list);


        File file = new File("/Users/zhangjianyong/IdeaProjects/waterrate/观江苑01.08xlsx.xls");
        File out = new File("/Users/zhangjianyong/IdeaProjects/waterrate/观江苑01.08_out.xls");


        HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(file));


        for (int i = 1; i <= 12; i++) {
            System.out.println("开始处理" + i + "号楼");
            HSSFSheet sheet = workbook.getSheet(i + "号楼");

            boolean lastFlag = false;

            for (int j = 2; j <= sheet.getLastRowNum(); j++) {

                HSSFRow row = sheet.getRow(j);
                HSSFCell cell = row.getCell(1);
                String stringCellValue = cell.getStringCellValue();
                setColor(row, workbook.createCellStyle(), HSSFColor.HSSFColorPredefined.WHITE);
                if (StrUtil.isBlank(stringCellValue)) {
                    if (lastFlag) {
                        System.out.println("房号：" + stringCellValue + ",修改");
                        //改为红色
                        setColor(row, workbook.createCellStyle(), HSSFColor.HSSFColorPredefined.YELLOW);
                    }
                } else {
                    if (contains(set, stringCellValue)) {
                        lastFlag = true;
                        set.remove(stringCellValue);
                        System.out.println("房号：" + stringCellValue + ",修改");
                        //改为红色
                        setColor(row, workbook.createCellStyle(), HSSFColor.HSSFColorPredefined.YELLOW);
                    } else {
                        lastFlag = false;
                    }
                }
            }

        }

        System.out.println("保存文件");
        BufferedOutputStream outputStream = new BufferedOutputStream(new FileOutputStream(out));
        workbook.write(outputStream);
        outputStream.flush();
        workbook.close();

        System.out.println(set.size());
        set.forEach(s -> System.out.println(s));

    }

    private static boolean contains(Set<String> set, String fh) {
        String[] split = fh.split("-");
        String fh2 = fh;
        if (split.length == 2) {
            fh2 = split[0] + "-1-" + split[1];
        }
        return set.contains(fh) || set.contains(fh2);
    }

    private static void setColor(HSSFRow row, HSSFCellStyle cellStyle, HSSFColor.HSSFColorPredefined colorPredefined) {


        /*cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        row.getCell(2).setCellStyle(cellStyle);*/
        HSSFCell cell = row.getCell(1);
        if (cell != null) {
            cellStyle.cloneStyleFrom(cell.getCellStyle());
            cellStyle.setFillForegroundColor(colorPredefined.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(cellStyle);
        }



        /*for (int i = 0; i < row.getLastCellNum(); i++) {
            HSSFCell cell = row.getCell(i);
            if (cell==null) continue;
            cellStyle.cloneStyleFrom(cell.getCellStyle());
            cellStyle.setFillForegroundColor(HSSFColor.HSSFColorPredefined.YELLOW.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(cellStyle);
        }*/

    }

    public static Sheet copySheet(XSSFWorkbook xssfWorkbook, SXSSFWorkbook workbook, String sheetName) {
        Sheet oldSheet = xssfWorkbook.getSheet(sheetName);
        Sheet newSheet = workbook.createSheet(sheetName);

        int lastRowNum = oldSheet.getLastRowNum();
        for (int i = 0; i < lastRowNum; i++) {
            copyRow(xssfWorkbook, workbook, sheetName, i, i);

        }

        return newSheet;
    }

    public static Row copyRow(XSSFWorkbook xssfWorkbook, SXSSFWorkbook workbook, String sheetName, int oldRowIndex, int newRowIndex) {
        Row oldRow = xssfWorkbook.getSheet(sheetName).getRow(oldRowIndex);
        Row newRow = workbook.getSheet(sheetName).createRow(newRowIndex);

        int oldLastCellIndex = oldRow.getLastCellNum();
        for (int i = 0; i < oldLastCellIndex; i++) {
            Cell cell = newRow.createCell(i);
            cell.setCellStyle(oldRow.getCell(i).getCellStyle());
        }
        return newRow;
    }

    public Row copyRow(SXSSFWorkbook workbook, int sheetIndex, int oldRowIndex, int newRowIndex) {
        Row oldRow = null;
        Row newRow = null;

        oldRow = workbook.getXSSFWorkbook().getSheetAt(sheetIndex).getRow(oldRowIndex);
        newRow = workbook.getSheetAt(sheetIndex).createRow(newRowIndex);

        int oldLastCellIndex = oldRow.getLastCellNum();
        for (int i = 0; i < oldLastCellIndex; i++) {
            Cell cell = newRow.createCell(i);
            cell.setCellType(oldRow.getCell(i).getCellType());
            cell.setCellStyle(oldRow.getCell(i).getCellStyle());
        }
        return newRow;
    }

    public void utilDownLoadExcel2() throws IOException {
        String path = "D:\\TestFile\\";
        String fileName = "test.xlsx";
        File file = new File(path + fileName);
        FileInputStream inputStream = new FileInputStream(file);
        XSSFWorkbook workbook = new XSSFWorkbook(inputStream);
        workbook.cloneSheet(0);
        workbook.setSheetName(1, "新Sheet页1");
        workbook.removeSheetAt(0);
        SXSSFWorkbook sxssfWorkbook = new SXSSFWorkbook(workbook, 100);
        for (int i = 2; i < 300000; i++) {
            Row newRow = copyRow(sxssfWorkbook, 0, 1, i);
            int lastCellIndex = newRow.getLastCellNum();
            for (int i1 = 0; i1 < lastCellIndex; i1++) {
                newRow.getCell(i1).setCellValue(Math.random() * 10000000);
            }
        }
        String outFileName = "testEnd-SXSSF.xlsx";
        FileOutputStream outFileStream = new FileOutputStream(new File(path + outFileName));
        BufferedOutputStream bufferedOutputStream = new BufferedOutputStream(outFileStream);
        sxssfWorkbook.write(bufferedOutputStream);
        bufferedOutputStream.flush();
        bufferedOutputStream.close();
    }

}
